package service;

import java.io.File;
import java.io.IOException;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;



/**
 * excel相关处理服务类
 * @author 陈凌志
 *
 */

public class ExcelService {

	private ProjectService ps = new ProjectService();
	
	
	/**
	 * 此方法用于把上传的excel文件导入数据库
	 * @param file 上传的excel文件
	 * @return key String类型 成功返回“导入成功”，失败返回“导入失”败或者“文件类型不是excel”
	 */
	public String readExcel(File file) {
		String key = isExcel(file.getName());
		if(key == null) {
			if(isExcel2003(file.getName())) {
				return readExcel2003(file);
			}else {
				return readExcel2007(file);
			}
		}else {
			return key;
		}
	}
	/**
	 * 2003版本前的excel文件导入
	 * @param file 上传的文件
	 * @return key String类型，成功返回“导入成功”，失败返回导入失败
	 */
	private String readExcel2003(File file) {
		String key = "";
		try {
			HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
			HSSFSheet sheet = workbook.getSheetAt(0);
			int firstRowNum = 1;
			int lastRowNum = sheet.getLastRowNum();
			for (int i = firstRowNum; i <=lastRowNum; i++) {
				HSSFRow row = sheet.getRow(i);
				//获取当前行最后单元格列号
				int lastCellNum = row.getLastCellNum();
				String[] value = new String[4];
				for (int j = 0; j < lastCellNum; j++) {
					HSSFCell cell = row.getCell(j);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					value[j] = cell.getStringCellValue();
				}
				Record user = new Record().set("userName", value[0])
						.set("pwd", value[1])
						.set("name", value[2])
						.set("power", value[3]);
				Db.save("user", user);
			}
			key = "导入成功";
			workbook.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			key = "导入失败";
		}
		return key;
		
	}
	/**
	 * 2003版本后的excel文件导入
	 * @param file 上传的文件
	 * @return key String类型，成功返回“导入成功”，失败返回导入失败
	 */
	private String readExcel2007(File file) {
		String key = "";
		try {
			XSSFWorkbook workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
			XSSFSheet sheet = workbook.getSheetAt(0);
			int firstRowNum = 1;
			int lastRowNum = sheet.getLastRowNum();
			for (int i = firstRowNum; i <=lastRowNum; i++) {
				XSSFRow row = sheet.getRow(i);
				String[] value = new String[4];
				for (int j = 0; j < 4; j++) {
					XSSFCell cell = row.getCell(j);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					value[j] = cell.getStringCellValue();
				}
				Record user = new Record().set("userName", value[0])
						.set("pwd", value[1])
						.set("name", value[2])
						.set("power", value[3]);

				Db.save("user", user);
			}
			key = "导入成功";
			workbook.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			key = "导入失败";
		}
		return key;
	}
	
	/**
	 * 此方法用于判断是否是excel文件
	 * @param fileName 上传的文件名
	 * @return String类型，是excel返回null，否则返回"文件类型不是EXCEL！"
	 */
	private String isExcel(String fileName) {
	     int iIndex = fileName.lastIndexOf(".");
	     String ext = (iIndex < 0) ? "" : fileName.substring(iIndex + 1).toLowerCase();
	     if (!"xls,xlsx".contains(ext) || "".contains(ext)) {
	    	 return "文件类型不是EXCEL！";
	     }else {
	    	 return null;
	     }
	}
	/**
	 * 此方法用于判断是否是2003版本前的excel
	 * @param filePath 上传后的文件路径
	 * @return boolean类型 是返回true，否则返回false
	 */
	private static boolean isExcel2003(String filePath)  {  
         return filePath.matches("^.+\\.(?i)(xls)$");  
     }  
	
	
	/**
	 * 此方法用于获取要到处的excel的表头
	 * @param type 要导出的excel是那个excel
	 * @return 成功返回String[]类型的表头，否则返回空
	 * 
	 */
	public String[] getThead(String type) {
		if(type.equals("project"))
			return projectThead() ;
		else if(type.equals("xmzt"))
			return xmztThead() ;
		else if(type.equals("end"))
			return endThead() ;
		else if(type.equals("mark"))
			return markThead() ;
		else
			return null;
	}
	
	/**
	 * 此方法用于获取要到处的excel的表内容
	 * @param type 要导出的excel是那个excel
	 * @return 成功返回String[]类型的表内容，否则返回空
	 * 
	 */
	public String[][] getTbody(String type) {
		if(type.equals("project"))
			return projectTbody() ;
		else if(type.equals("xmzt"))
			return xmztTbody() ;
		else if(type.equals("end"))
			return endTbody() ;
		else if(type.equals("mark"))
			return markTbody(markThead()) ;
		else
			return null;
	}

	/**
	 * 此方法用于获取立项项目的表内容
	 * @return String[][]类型，为立项项目的表内容
	 */
	private String[][] projectTbody() {
		// TODO Auto-generated method stub
		List<Record> projectTbodyList = ps.queryProjectByStatus("立项", Calendar.getInstance().get(Calendar.YEAR));
		String[][] projectTbody = new String[projectTbodyList.size()][12];
		for(int i = 0; i<projectTbodyList.size();i++) {
				projectTbody[i][0] = projectTbodyList.get(i).getStr("projectId");
				projectTbody[i][1] = projectTbodyList.get(i).getStr("name");
				projectTbody[i][2] = projectTbodyList.get(i).getStr("type");
				projectTbody[i][3] = projectTbodyList.get(i).getStr("personsResponsibleName");
				projectTbody[i][4] = projectTbodyList.get(i).getStr("personsResponsibleNumber");
				projectTbody[i][5] = projectTbodyList.get(i).getStr("number");
				projectTbody[i][6] = projectTbodyList.get(i).getStr("otherPeople");
				projectTbody[i][7] = projectTbodyList.get(i).getStr("teacherName");
				projectTbody[i][8] = projectTbodyList.get(i).getStr("title");
				projectTbody[i][9] = projectTbodyList.get(i).getStr("code");
				projectTbody[i][10] = projectTbodyList.get(i).getStr("introduction");
		}
		return projectTbody;
	}

	/**
	 * 此方法用于获取立项项目的表头
	 * @return String[]类型，为立项项目的表头
	 */
	private String[] projectThead() {
		// TODO Auto-generated method stub
		String[] projectThead= {"项目编号","项目名称","项目类型","项目负责人姓名" ,"项目负责人学号","参与学生人数","项目其他成员信息","指导教师姓名","指导教师职称","项目所属一级学科代码","项目简介"}; 
		return projectThead;
	}
	/**
	 * 此方法用于获取项目状态数据表内容
	 * @return String[][]类型，为立项项目的表内容
	 */
	private String[][] xmztTbody() {
		// TODO Auto-generated method stub
		int size = 0;
		List<Record> xmztTbodyList = ps.queryProjectByStatus("立项", Calendar.getInstance().get(Calendar.YEAR));
		for(int i = 0;i < xmztTbodyList.size();i++) {
			size++;
			String[] otherPeople = xmztTbodyList.get(i).getStr("otherPeople").split(",");
			for(int j = 0;j<otherPeople.length;j++)
				size++;
		}
		String[][] xmztTbody = new String[size][5];
		size = 0;
		for(int i = 0;i < xmztTbodyList.size();i++) {
			xmztTbody[size][0] = xmztTbodyList.get(i).getStr("personsResponsibleNumber");
			xmztTbody[size][1] = xmztTbodyList.get(i).getStr("personsResponsibleName");
			xmztTbody[size][2] = xmztTbodyList.get(i).getStr("name");
			xmztTbody[size][3] = xmztTbodyList.get(i).getStr("levels");
			xmztTbody[size][4] = xmztTbodyList.get(i).getStr("type");
			size++;
			String[] otherPeople = xmztTbodyList.get(i).getStr("otherPeople").split(",");
			for(int j = 0;j < otherPeople.length;j++) {
				String[] people = otherPeople[j].split("/");
				xmztTbody[size][0] = people[1];
				xmztTbody[size][1] = people[0];
				xmztTbody[size][2] = xmztTbodyList.get(i).getStr("name");
				xmztTbody[size][3] = xmztTbodyList.get(i).getStr("levels");
				xmztTbody[size][4] = xmztTbodyList.get(i).getStr("type");
				size++;
			}
		}
		return xmztTbody;
	}

	/**
	 * 此方法用于获取项目状态数据表的表头
	 * @return String[]类型，为立项项目的表头
	 */
	private String[] xmztThead() {
		// TODO Auto-generated method stub
		String[] xmztThead= {"学号","学生姓名","项目名称","项目级别" ,"项目类别"}; 
		return xmztThead;
	}
	
	/**
	 * 此方法用于获取结项项目的表内容
	 * @return String[][]类型，为立项项目的表内容
	 */
	private String[][] endTbody() {
		// TODO Auto-generated method stub
		List<Record> projectTbodyList = ps.queryProjectByStatus("结项", Calendar.getInstance().get(Calendar.YEAR));
		String[][] projectTbody = new String[projectTbodyList.size()][12];
		for(int i = 0; i<projectTbodyList.size();i++) {
				projectTbody[i][0] = projectTbodyList.get(i).getStr("projectId");
				projectTbody[i][1] = projectTbodyList.get(i).getStr("levels");
				projectTbody[i][2] = projectTbodyList.get(i).getStr("organization");
				projectTbody[i][3] = projectTbodyList.get(i).getStr("name");
				projectTbody[i][4] = projectTbodyList.get(i).getStr("type");
				projectTbody[i][5] = projectTbodyList.get(i).getStr("personsResponsibleName");
				projectTbody[i][6] = projectTbodyList.get(i).getStr("personsResponsibleNumber");
				projectTbody[i][7] = projectTbodyList.get(i).getStr("number");
				projectTbody[i][8] = projectTbodyList.get(i).getStr("otherPeople");
				projectTbody[i][9] = projectTbodyList.get(i).getStr("teacherName");
				projectTbody[i][10] = projectTbodyList.get(i).getStr("paper");
		}
		return projectTbody;
	}

	/**
	 * 此方法用于获取结项项目的表头
	 * @return String[]类型，为立项项目的表头
	 */
	private String[] endThead() {
		// TODO Auto-generated method stub
		String[] projectThead= {"项目编号","级别","报送单位","项目名称","项目类型","项目负责人姓名" ,"项目负责人学号","参与学生人数","项目其他成员信息","指导教师姓名","发表论文篇数和期刊名称，论文题目及发表页码（多少页至多少页）"}; 
		return projectThead;
	}
	
	/**
	 * 此方法用于获取评分的表内容
	 * @return String[][]类型，为立项项目的表内容
	 */
	private String[][] markTbody(String [] thead) {
		// TODO Auto-generated method stub
		MarkService ms = new MarkService();
		List<Record> markTbodyList = ps.queryProjectByStatus("待审核立项", Calendar.getInstance().get(Calendar.YEAR));
		String[][] markTbody = new String[markTbodyList.size()][thead.length];
		for(int i = 0; i<markTbodyList.size();i++) {
			markTbody[i][0] = markTbodyList.get(i).getStr("organization");
			markTbody[i][1] = markTbodyList.get(i).getStr("name");
			markTbody[i][2] = markTbodyList.get(i).getStr("personsResponsibleName");
			markTbody[i][3] = markTbodyList.get(i).getStr("teacherName");
			for(int j = 4 ; j < thead.length ; j++) {
				List<Record> mark = ms.queryMarkByPIDAndTeacher(markTbodyList.get(i).getInt("id"), thead[j]);
				if(mark.size() != 0)
					markTbody[i][j] = mark.get(0).getStr("mark");
			}
		}
		return markTbody;
	}
	
	/**
	 * 此方法用于获取评分数据表的表头
	 * @return String[]类型，为评分的表头
	 */
	private String[] markThead() {
		// TODO Auto-generated method stub
		LoginService ls = new LoginService();
		List<Record> teacher = ls.queryTeacher();
		String [] markThead = new String [teacher.size()+4];
		markThead[0] = "报送单位";
		markThead[1] = "项目名称";
		markThead[2] = "负责人姓名";
		markThead[3] = "指导教师";
		for(int i = 0; i < teacher.size() ; i++) {
			markThead[i+4] = teacher.get(i).getStr("name");
		}
		return markThead;
	}
	
}
